create database EmployeeTransferManager

GO
use EmployeeTransferManager

GO
create table Employee
(	EmpNumber varchar(30) primary key,
	Password varchar(20),
	EmpName nvarchar(50),
	Roles varchar(50),
	Address nvarchar(50) ,
	Phone nvarchar(15),
	Email nvarchar(25),
	WorkExp int,
	Birthday datetime,
	CurrentLocation nvarchar(50),
	CurrentProject nvarchar(50),
	CurrentDepartment nvarchar(50),
	Allowance float,
)
GO
insert into Employee values('E01','E01','Thieu Nghi','Manager','87 Tam Trinh','01685932953','thieuquocnghi@gmail.com',4,'8/9/1990','Ha Nam','Transfer Manager','Programing',6900);
insert into Employee values('E02','E02','Khuat Lan','Manager','45 Bach Dang','0987578635','khuatdanglen@gmail.com',6,'1/7/1990','Ha Noi','Transfer Manager','Accounting',4300);
insert into Employee values('CP01','CP01','Bui Tung','Employee','98 Minh Khai','0989894066','buithanhtung@gmail.com',3,'1/12/1990','Ha Noi','Transfer Manager','Planning',4200);
insert into Employee values('CP02','CP02','Son Tung','Employee','76 Quan Nhan','0978962547','sontungdkbg@gmail.com',5,'10/5/1980','Bac Giang','Transfer Manager','Personal',7500);
insert into Employee values('CP03','CP03','Anh Viet','Employee','5 Quang Trung','0914577975','David@gmail.com',4,'2/14/1988','Ha Noi','Transfer Manager','Programing',4700);

GO
create table Location
(	
	Names Nvarchar(100)primary key,
	Allowance float
)
GO
insert into Location values('Ha Nam',1300)
insert into Location values('Ha Noi',5000)
insert into Location values('Bac Giang',2300)
insert into Location values('Thai Binh',1000)

GO
create table Project
(	
	Names nvarchar(100)primary key,
	Allowance float
)
GO
insert into Project values('Transfer Manager',1000)
insert into Project values('Medical Store Billing',500)
insert into Project values('Demo Project',900)

GO
create table Department
(	
	Names nvarchar(100)primary key,
	Allowance float
)
GO
insert into Department values('Accounting',2000)
insert into Department values('Personal',2800)
insert into Department values('Planning',1900)
insert into Department values('Programing',3600)

GO
create table Transfer
(	ID int identity primary key,
	EmpNumber varchar(30) foreign key references Employee(EmpNumber),
	EmpName nvarchar(50),
	FromLocation nvarchar(100)foreign key references Location(Names),
	ToLocation nvarchar(100)foreign key references Location(Names),
	FromProject nvarchar(100)foreign key references Project(Names),
	ToProject nvarchar(100)foreign key references Project(Names),
	FromDept nvarchar(100) foreign key references Department(Names),
	ToDept nvarchar(100)foreign key references Department(Names),
	Allowance float,
	RelievingDate datetime,
	JoiningDate datetime,	
	Sender nvarchar(50),
	Approval varchar(10),
	Agree varchar(10),	
	Reason nvarchar(300)
)
GO
insert into Transfer values('CP01','Bui Tung','Thai Binh','Thai Binh','Transfer Manager','Transfer Manager','Programing','Accounting',4300,'6/24/2010','8/24/2010','E01','Yes','Yes','Hoi Buon')
insert into Transfer values('CP01','Bui Tung','Thai Binh','Ha Noi','Transfer Manager','Transfer Manager','Accounting','Accounting',5300,'8/24/2010','3/24/2011','CP01','Yes','Yes','Cong viec moi chac la tot')
insert into Transfer values('CP02','Son Tung','Bac Giang','Ha Nam','Transfer Manager','Transfer Manager','Accounting','Accounting',5300,'1/28/2009','3/28/2010','E02','Yes','Yes','Chia tay')
insert into Transfer values('CP02','Son Tung','Ha Nam','Ha Noi','Transfer Manager','Transfer Manager','Accounting','Personal',8000,'5/28/2010','12/28/2010','CP02','Yes','Yes','Khang dinh')
insert into Transfer values('CP02','Son Tung','Ha Noi','Thai Binh','Transfer Manager','Demo Project','Personal','Personal',4700,'12/28/2010','10/28/2011','CP02','Yes','No','Muon lam Demo project')
insert into Transfer values('CP03','Anh Viet','Ha Noi','Thai Binh','Transfer Manager','Transfer Manager','Accounting','Personal',8800,'2/15/2010','8/15/2010','E01','Yes','Yes','')
insert into Transfer values('CP03','Anh Viet','Thai Binh','Bac Giang','Transfer Manager','Transfer Manager','Personal','Personal',6100,'8/15/2010','12/15/2010','CP03','No','No','Chac la kha hon')
